package com.cm309.hospital.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.cm309.hospital.model.Subject;

public class SubjectDao {
	public static MyDataSource mds = new MyDataSource();
	
	public List<Subject> queryAll() {
		List<Subject> subjects = new ArrayList<Subject>();

		try {
			
			Connection conn = mds.getConnection();
			PreparedStatement ps = null;
			try {
				ps = conn.prepareStatement("SELECT * FROM Subject ");
				ResultSet rs = ps.executeQuery();

				while (rs.next()) {
					Subject c = new Subject(rs.getString("subject_id"),
							rs.getString("subject_name"), rs.getString("description"));
					subjects.add(c);
				}

			} finally {
				ps.close();
			}
			return subjects;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return null;
	}

	
	public Subject querySingle(String subject_id) {
		Subject subject = null;
		try {
			Connection conn = mds.getConnection();
			PreparedStatement ps = null;
			try {
				ps = conn.prepareStatement("SELECT * FROM Subject WHERE subject_id = ?");
				ps.setString(1, subject_id);
				ResultSet rs = ps.executeQuery();

				if (rs.next()) {
					subject = new Subject(rs.getString("subject_id"),
							rs.getString("subject_name"), rs.getString("description"));
				}

			} finally {
				ps.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return subject;
		
	}

}
